package srv;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class MobileOffers {

	private static final String CONFIG_FILE = "configuration.properties";

	private Connection connection;

	private Connection getConnection() throws ClassNotFoundException,
			SQLException {
		if (connection == null) {
			try {
				Properties props = new Properties();
				InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream(CONFIG_FILE);
				props.load(is);
				
//				String driver = "com.ibm.db2.jcc.DB2Driver";
//				String dbUrl = "jdbc:db2://192.168.86.128:50000/MOBILE";
//				String username = "Administrator";
//				String password = "P@ssw0rd";
				String driver = props.getProperty("driver");
				String dbUrl = props.getProperty("dbUrl");
				String username = props.getProperty("username");
				String password = props.getProperty("password");
				Class.forName(driver);
				String url = dbUrl;
				connection = DriverManager.getConnection(url, username,
						password);

			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException ioex) {
				ioex.printStackTrace();
			}
		}

		return connection;
	}

	/**
	 * Provide all available car brands
	 * 
	 * @return comma separated string representing the id and name of each brand
	 */
	public String getAllBrands() {
		String allBrandsSql = "select ID, NAME from BRAND";
		Statement statement = null;
		StringBuilder sb = new StringBuilder("id,name\n");

		try {
			statement = getConnection().createStatement();
			ResultSet rs = statement.executeQuery(allBrandsSql);

			while (rs.next()) {
				int id = rs.getInt("ID");
				String name = rs.getString("NAME");

				sb.append(id + "," + name + "\n");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return sb.toString();
	}

	/**
	 * Get car models that relate to a specified brand
	 * 
	 * @param brandId
	 *            brand id for the model
	 * @return the model that related to the provided brand id
	 */
	public String getModelsByBrand(int brandId) {
		String modelsSql = "select ID, NAME from MODEL where BRAND_ID = ?";
		PreparedStatement statement = null;
		StringBuilder sb = new StringBuilder("id,name\n");

		try {
			statement = getConnection().prepareStatement(modelsSql);
			statement.setInt(1, brandId);

			ResultSet rs = statement.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("ID");
				String name = rs.getString("NAME");

				sb.append(id + "," + name + "\n");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return sb.toString();
	}

	public String getOffersByFilter(int brandId, int modelId, int yearUp,
			int yearDown, double priceUp, double priceDown) {
		String offersSql = "select " + "brand.NAME as BRAND_NAME, "
				+ "model.NAME as MODEL_NAME, " + "offer.MAKE_YEAR, "
				+ "offer.PRICE, " + "offer.CONTACT " + "  from OFFERS offer ";
		offersSql += " inner join BRAND brand on offer.BRAND_ID = brand.ID ";
		offersSql += " inner join MODEL model on offer.MODEL_ID = model.ID ";
		String whereClause = getWhereClauseForOffers(brandId, modelId, yearUp,
				yearDown, priceUp, priceDown);
		offersSql += whereClause;

		System.out.println(offersSql);

		Statement statement = null;
		StringBuilder sb = new StringBuilder("brand,model,year,price,contact\n");

		try {
			statement = getConnection().createStatement();

			ResultSet rs = statement.executeQuery(offersSql);

			while (rs.next()) {
				String brand = rs.getString("BRAND_NAME");
				String model = rs.getString("MODEL_NAME");
				int year = rs.getInt("MAKE_YEAR");
				double price = rs.getDouble("PRICE");
				String contact = rs.getString("CONTACT");

				sb.append(brand + "," + model + "," + year + "," + price + ","
						+ contact + "\n");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return sb.toString();
	}

	public void insertOffer(int brandId, int modelId, int year, double price,
			String contact) {
		String insertSql = " insert into OFFERS(BRAND_ID, MODEL_ID, MAKE_YEAR, PRICE, CONTACT, UPDATED) ";
		insertSql += " values(?,?, ?, ?, ?, current timestamp) ";

		PreparedStatement statement = null;

		try {
			statement = getConnection().prepareStatement(insertSql);
			statement.setInt(1, brandId); // set brand id
			statement.setInt(2, modelId); // set model id
			statement.setInt(3, year); // set make year
			statement.setDouble(4, price); // set price
			statement.setString(5, contact); // set contact

			statement.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	private String getWhereClauseForOffers(int brandId, int modelId,
			int yearUp, int yearDown, double priceUp, double priceDown) {
		StringBuilder whereClause = new StringBuilder();
		whereClause.append(" where offer.BRAND_ID = " + brandId + " ");
		whereClause.append(" and offer.MODEL_ID = " + modelId + " ");
		whereClause.append(" and offer.MAKE_YEAR <= " + yearUp + " ");
		whereClause.append(" and offer.MAKE_YEAR >= " + yearDown + " ");
		whereClause.append(" and offer.PRICE <= " + priceUp + " ");
		whereClause.append(" and offer.PRICE >= " + priceDown + " ");

		return whereClause.toString();
	}

	@Override
	protected void finalize() {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
